package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import ModelLayer.Employee;
import ModelLayer.Guest;
import ModelLayer.KeyWord;
import ModelLayer.RoomBooking;
import ModelLayer.Sale;

public class DBSale implements IFDBSale {
	private Connection con;

	public DBSale() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int updateSalePrice(int id, double price) {
		int rc = -1;
		String query = "UPDATE sale SET " + "totalPrice = '" + price + "'"
				+ " WHERE saleId = '" + id + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in sale price db: " + e);
		}
		return rc;
	}

	@Override
	public Sale getSale(int saleId, boolean retriveAssociation) {
		String wClause = "  saleId = '" + saleId + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insertSale(Sale sale) {
		int nextId = GetMaxId.getMaxId("Select max(saleId) from sale") + 1;
		System.out.println("next nextId = " + nextId);

		String payDate = "";
		if (sale.getPayDate() != null)
			payDate = new SimpleDateFormat("dd/MM/yyyy").format(sale
					.getPayDate());
		String payMethod = "";
		if (sale.getPayMethod() != null)
			payMethod = sale.getPayMethod().toString();
		String query = "INSERT INTO sale(saleId, g_Id, e_Id, totalPrice, payStatus, createDate, payDate, payMethod) VALUES('"
				+ nextId
				+ "','"
				+ sale.getGuest().getID()
				+ "','"
				+ sale.getEmployee().getID()
				+ "','"
				+ sale.getTotalPrice()
				+ "','"
				+ sale.getPayStatus().toString()
				+ "','"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(sale
						.getCreateDate()))
				+ "','"
				+ payDate
				+ "','"
				+ payMethod + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("Sale is not inserted");
		}
		return nextId;
	}

	@Override
	public int updateSale(Sale sale) {
		Sale saleObj = sale;
		int rc = -1;

		String payDate = "";
		if (sale.getPayDate() != null)
			payDate = new SimpleDateFormat("dd/MM/yyyy").format(sale
					.getPayDate());
		String payMethod = "";
		if (sale.getPayMethod() != null)
			payMethod = sale.getPayMethod().toString();

		String query = "UPDATE sale SET " + "g_Id = '"
				+ saleObj.getGuest().getID()
				+ "',"
				+ "e_Id = '"
				+ saleObj.getEmployee().getID()
				+ "',"
				+ "totalPrice = '"
				+ saleObj.getTotalPrice()
				+ "',"
				+ "payStatus = '"
				+ saleObj.getPayStatus().toString()
				+ "',"
				+ "createDate = '"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(sale
						.getCreateDate())) + "'," + "payDate = '" + payDate
				+ "'," + "payMethod = '" + payMethod + "'"
				+ " WHERE saleId = '" + saleObj.getId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in sale db: " + e);
		}
		return rc;
	}

	@Override
	public int deleteSale(int saleId) {
		int rc = -1;
		String query = "DELETE FROM sale WHERE saleId = '" + saleId + "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in sale db: " + ex);
		}
		return rc;
	}

	@Override
	public LinkedList<Sale> getAllSale(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	private Sale singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Sale sale = new Sale();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				sale = buildSale(results);
				stmt.close();
				if (retrieveAssociation) {
					IFDBGuest dbGuest = new DBGuest();
					Guest guest = dbGuest.getGuest(sale.getGuest().getID(),
							true);
					sale.setGuest(guest);
					System.out.println("Guest is selected");

					IFDBRoomBooking dbRB = new DBRoomBooking();
					RoomBooking rb = dbRB.getRoomBooking(sale.getId(), true);
					sale.setRoomBooking(rb);
					System.out.println("Roombooking is selected");

					IFDBEmployee dbEmployee = new DBEmployee();
					Employee employee = dbEmployee.getEmployee(sale
							.getEmployee().getID(), true);
					sale.setEmployee(employee);
					System.out.println("Employee is selected");
				}
			} else {
				sale = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return sale;
	}

	private LinkedList<Sale> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		LinkedList<Sale> list = new LinkedList<Sale>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next()) {
				Sale sale = new Sale();
				sale = buildSale(results);
				list.add(sale);
			}
			stmt.close();
			if (retrieveAssociation) {
				for (Sale sale : list) {
					IFDBGuest dbGuest = new DBGuest();
					Guest guest = dbGuest.getGuest(sale.getGuest().getID(),
							true);
					sale.setGuest(guest);
					System.out.println("Guest is selected");

					IFDBRoomBooking dbRB = new DBRoomBooking();
					RoomBooking rb = dbRB.getRoomBooking(sale.getId(), true);
					sale.setRoomBooking(rb);
					System.out.println("Roombooking is selected");

					IFDBEmployee dbEmployee = new DBEmployee();
					Employee employee = dbEmployee.getEmployee(sale
							.getEmployee().getID(), true);
					sale.setEmployee(employee);
					System.out.println("Employee is selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Sale buildSale(ResultSet results) throws ParseException,
			SQLException {
		Sale sale = new Sale();
		try {
			sale.setId(results.getInt("saleId"));
			sale.setGuest(new Guest(results.getInt("g_Id")));
			sale.setEmployee(new Employee(results.getInt("e_Id")));
			sale.setTotalPrice(results.getDouble("totalPrice"));
			sale.setPayStatus(KeyWord.valueOf(results.getString("payStatus")));
			sale.setCreateDate(new SimpleDateFormat("dd/MM/yyyy").parse(results
					.getString("createDate")));
			if (!results.getString("payDate").isEmpty())
				sale.setPayDate(new SimpleDateFormat("dd/MM/yyyy")
						.parse(results.getString("payDate")));
			if (!results.getString("payMethod").isEmpty())
				sale.setPayMethod(KeyWord.valueOf(results
						.getString("payMethod")));
		} catch (Exception e) {
			System.out.println("error in building the sale object");
		}
		return sale;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT saleId, g_Id, e_Id, totalPrice, payStatus, createDate, payDate, payMethod FROM sale";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}

}
